import pymysql
import pymysql.cursors
from rest_framework.response import Response
import redis
from django.conf import settings as stg


SQL = {
     #车间最高温湿度，车间最低温湿度，车间平均温湿度
    "station_avg_th":"SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d'),MAX(a.Tem),MAX(a.Tem),MIN(a.Tem),round(AVG(a.Tem),2),MAX(a.Hum),MIN(a.Hum),round(AVG(a.Hum),2) FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d')",
     #车间实时温湿度
     #"station_now_th": "SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v ,round(AVG(a.Tem),2),round(AVG(a.Hum),2),MAX(a.Tem),MIN(a.Tem),MAX(a.Hum),MIN(a.Hum) FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour) GROUP BY h,v",

     "station_now_th":"SELECT h,v,avg_tem,avg_hum,max_tem,min_tem,max_hum,min_hum FROM `vies_th_by_hour` WHERE \
     rc> DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') AND shift = IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N') GROUP BY h ORDER BY h",


    "station_now_th_y": "SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v ,round(AVG(a.Tem),2),round(AVG(a.Hum),2),MAX(a.Tem),MIN(a.Tem),MAX(a.Hum),MIN(a.Hum) FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 20 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 32.5 hour) GROUP BY h,v",

    #区域最高温湿度，最低温湿度
    "region_th":"SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d') ,MAX(a.Tem),MIN(a.Tem),round(AVG(a.Tem),2),MAX(a.Hum),MIN(a.Hum),round(AVG(a.Hum),2), a.Controller FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.Controller='{}' AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d')",



     #周区域最高温湿度，最低温湿度
     "week_region_th":"SELECT week(a.in_time),MAX(a.Tem),MIN(a.Tem),round(AVG(a.Tem),2),MAX(a.Hum),MIN(a.Hum),round(AVG(a.Hum),2)\
                       FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.Controller='{}' AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<timestamp(adddate(date('{}'),0)) GROUP BY week(a.in_time)",

     #周车间最高温湿度，最低温湿度
     "week_station_avg_th":"SELECT week(a.in_time),MAX(a.Tem),MIN(a.Tem),round(AVG(a.Tem),2),MAX(a.Hum),MIN(a.Hum),round(AVG(a.Hum),2)\
                            FROM tb_first_th a,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}'  AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<timestamp(adddate(date('{}'),0)) GROUP BY week(a.in_time)",

    # 正常设备运行占比(实时1分钟) 31  /  72
    # "Normal_operation":"SELECT count(DISTINCT(a.Controller))\
    #                     FROM tb_first_running a ,st126_device b \
    #                     WHERE a.Controller=b.gw_name and a.Green='1' and a.Yellow='0' AND a.Red='0' \
    #                     AND {} \
    #                     a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",
    "Normal_operation": "SELECT count(*) FROM view_machine_state  where {} Green = 1",
    # 異常設備佔比(实时1分钟)
    # "Abnormal_operation":"SELECT COUNT(a.Controller)/(SELECT COUNT(a.Controller) FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name  AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW())FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name and a.Green='0' and a.Red='1' and a.Yellow='0' AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",

    # "Abnormal_operation": "SELECT count(DISTINCT(a.Controller))\
    #                        FROM tb_first_running a ,st126_device b \
    #                        WHERE a.Controller=b.gw_name \
    #                        and ((a.Green='0' and a.Red='1' and a.Yellow='0')OR(a.Green='1' and a.Red='1' and a.Yellow='0')) \
    #                        AND {} \
    #                         a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",
    "Abnormal_operation": " SELECT count(*) FROM view_machine_state  where {} Red = 1 ",
    # 等待設備佔比(实时1分钟)
    # "Wait_operation":"SELECT COUNT(a.Controller)/(SELECT COUNT(a.Controller) FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name  AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW())FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name and a.Green='0' and a.Red='0' and a.Yellow='1' AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",


    # "Wait_operation": "SELECT count(DISTINCT(a.Controller))\
    #                    FROM tb_first_running a ,st126_device b \
    #                    WHERE a.Controller=b.gw_name \
    #                    and ((a.Green='0' and a.Red='0' and a.Yellow='1')OR(a.Green='1' and a.Red='1' and a.Yellow='1')) \
    #                    AND {}\
    #                     a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",

    "Wait_operation": "SELECT count(*) FROM view_machine_state  where {} Yellow = 1  ",
    # 停機設備佔比(实时1分钟)
    # "Shutdown_operation":"SELECT COUNT(a.Controller)/(SELECT COUNT(a.Controller) FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name  AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW())FROM tb_first_running a ,st126_device b WHERE ({}) AND a.Controller=b.gw_name and a.Green='0' and a.Red='0' and a.Yellow='0' AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",

    # "Shutdown_operation": "SELECT count(DISTINCT(a.Controller))``\
    #                        FROM tb_first_running a ,st126_device b\
    #                        WHERE a.Controller=b.gw_name and a.Green='0' and a.Yellow='0' AND a.Red='0' \
    #                        AND {} \
    #                         a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",

    "Shutdown_operation": "SELECT count(*) FROM view_machine_state  where {} Green=0 and Yellow=0 AND Red=0 ",

    "sum_four_operation": "SELECT count(*) FROM view_machine_state  where {} 1=1",


    #各设备的具体ID
     "all_equipment_id":"SELECT DISTINCT(a.Controller) FROM tb_first_running a ,st126_device b WHERE  b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.station='{}' AND  a.Controller=b.gw_name AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()",



     #當天單臺設備運行正常時間(总时间8-20)
     "Run_normal_time":"SELECT COUNT(*)/6 \
                        FROM tb_first_running a , st126_device b \
                        WHERE  a.Controller=b.gw_name \
                        AND {}\
                        a.Green='1' and a.Red='0' and a.Yellow='0'AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour)AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)",



     #當天單臺異常時間(总时间8-20)
    #"Abnormal_time":"SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d'),COUNT(*)/360 FROM tb_first_running a , st126_device b WHERE  a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.station='{}' AND a.Controller='{}'and ((a.Green='0' and a.Red='1' and a.Yellow='0')OR(a.Green='1' and a.Red='1' and a.Yellow='0'))AND a.in_time>=timestamp(adddate(date('{}'),0))AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d')",

    "Abnormal_time":"SELECT COUNT(*)/6 \
                     FROM tb_first_running a , st126_device b \
                     WHERE  a.Controller=b.gw_name \
                     AND {}\
                     ((a.Green='0' and a.Red='1' and a.Yellow='0')OR(a.Green='1' and a.Red='1' and a.Yellow='0'))AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour)AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)",


    #當天單臺待機時間(总时间8-20)
    #"Wait_time": "SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d'),COUNT(*)/360 FROM tb_first_running a , st126_device b WHERE  a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.station='{}' AND a.Controller='{}'and ((a.Green='0' and a.Red='0' and a.Yellow='1')OR(a.Green='0' and a.Red='1' and a.Yellow='1'))AND a.in_time>=timestamp(adddate(date('{}'),0))AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d')",

     "Wait_time":"SELECT COUNT(*)/6 \
                  FROM tb_first_running a , st126_device b \
                  WHERE  a.Controller=b.gw_name \
                  AND {}\
                  ((a.Green='0' and a.Red='0' and a.Yellow='1')OR(a.Green='1' and a.Red='0' and a.Yellow='1')OR(a.Green='0' and a.Red='1' and a.Yellow='1')) AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour)AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)",



    #當天單臺停機時間(总时间8-20)
     #"Down_time": "SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d'),COUNT(*)/360 FROM tb_first_running a , st126_device b WHERE  a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.station='{}' AND a.Controller='{}'and a.Green='0' and a.Red='0' and a.Yellow='0'AND a.in_time>=timestamp(adddate(date('{}'),0))AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d')",

     "Down_time":"SELECT COUNT(*)/6 \
                  FROM tb_first_running a , st126_device b \
                  WHERE  a.Controller=b.gw_name \
                  AND {} \
                  a.Green='0' and a.Red='0' and a.Yellow='0' AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour)AND a.in_time<date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)",



     #當天單臺正常時間(分钟)1
     "Run_normal_time_m1":"SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                        FROM tb_first_running a ,st126_device b \
                        WHERE a.Controller=b.gw_name AND {}\
                        a.Controller='{}'AND a.Green='1' and a.Red='0' and a.Yellow='0'AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour) \
                        GROUP BY h,v,a.Controller",

    # 當天單臺正常時間(分钟)
    "Run_normal_time_m": "SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                   FROM tb_first_running a ,st126_device b \
                   WHERE a.Controller=b.gw_name AND {}\
                   a.Green='1' and a.Red='0' and a.Yellow='0'AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour) \
                   GROUP BY h,v,a.Controller",






    #當天單臺異常時間(分钟)1
     "Abnormal_time_m1":"SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                        FROM tb_first_running a ,st126_device b \
                        WHERE a.Controller=b.gw_name AND {}\
                        a.Controller='{}' AND ((a.Green='0' and a.Red='1' and a.Yellow='0')OR(a.Green='1' and a.Red='1' and a.Yellow='0'))AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour)\
                        GROUP BY h,v,a.Controller",

     #當天單臺異常時間(分钟)
     "Abnormal_time_m":"SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                        FROM tb_first_running a ,st126_device b \
                        WHERE a.Controller=b.gw_name AND {}\
                        ((a.Green='0' and a.Red='1' and a.Yellow='0')OR(a.Green='1' and a.Red='1' and a.Yellow='0'))AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour)\
                        GROUP BY h,v,a.Controller",




    # 當天單臺待機時間(分钟)
    "Wait_time_m1": "SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                   FROM tb_first_running a ,st126_device b \
                   WHERE a.Controller=b.gw_name AND {}\
                   a.Controller='{}' AND ((a.Green='0' and a.Red='0' and a.Yellow='1')OR(a.Green='1' and a.Red='0' and a.Yellow='1')OR(a.Green='0' and a.Red='1' and a.Yellow='1'))AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour)\
                   GROUP BY h,v,a.Controller",


     #當天單臺待機時間(分钟)
     "Wait_time_m":"SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                        FROM tb_first_running a ,st126_device b \
                        WHERE a.Controller=b.gw_name AND {}\
                        ((a.Green='0' and a.Red='0' and a.Yellow='1')OR(a.Green='1' and a.Red='0' and a.Yellow='1')OR(a.Green='0' and a.Red='1' and a.Yellow='1'))AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour)\
                        GROUP BY h,v,a.Controller",

    # 當天單臺停機時間(分钟)1
    "Down_time_m1": "SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                   FROM tb_first_running a ,st126_device b \
                   WHERE a.Controller=b.gw_name AND {}\
                   a.Controller='{}' AND a.Green='0' and a.Red='0' and a.Yellow='0'AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour) \
                   GROUP BY h,v,a.Controller",


     #當天單臺停機時間(分钟)
     "Down_time_m":"SELECT HOUR(a.in_time)+(0.5*FLOOR(MINUTE(a.in_time) / 30)) AS h, FLOOR(MINUTE(a.in_time) / 30) AS v,(COUNT(a.Controller))/6 ,a.Controller\
                        FROM tb_first_running a ,st126_device b \
                        WHERE a.Controller=b.gw_name AND {}\
                        a.Green='0' and a.Red='0' and a.Yellow='0'AND a.in_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 21 hour) \
                        GROUP BY h,v,a.Controller",



    #一天的設備稼動率
     #"Day_utilization_rate":"SELECT (COUNT(*)-1)/360/24 FROM tb_first_running a WHERE a.Controller='{}'and a.Green='1' AND a.in_time>=timestamp(adddate(date(NOW()),0)) AND a.in_time<=date_add(timestamp(adddate(date(NOW()),0)), interval 1 day)",

     "Day_utilization_rate":"SELECT a.Controller,DATE_FORMAT(a.in_time,'%Y-%m-%d'),round(COUNT(*)/360/24/(DATEDIFF('{}','{}')+1),2)\
                        FROM tb_first_running a , st126_device b \
                        WHERE  a.Controller=b.gw_name \
                        AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.station='{}' AND b.provider='{}'  and b.level_1='{}'\
                        and a.Green='1' and a.Red='0' and a.Yellow='0'AND a.in_time>=timestamp(adddate(date('{}'),0))AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 1 day) \
                        GROUP BY a.Controller",


     #同類型設備實時稼動率
     "Real_time_utilization_rate":"SELECT (COUNT(*)-1)/360/(SELECT count(DISTINCT(a.Controller)) FROM  tb_first_running a, st126_device b WHERE a.Controller=b.gw_name AND b.func_desc='三色灯信号采集')/(select round(TIMESTAMPDIFF(second,timestamp(adddate(date(sysdate()),0)),NOW())/3600,2)) FROM tb_first_running a WHERE  a.Green='1' AND a.in_time>=timestamp(adddate(date(NOW()),0)) AND a.in_time<=NOW()",


     #平均稼動率
      #"Avg_utilization_rate":"SELECT (COUNT(*)-1)/360/(SELECT count(DISTINCT(a.Controller)) FROM  tb_first_running a, st126_device b WHERE a.Controller=b.gw_name AND b.func_desc='三色灯信号采集')/(select round(TIMESTAMPDIFF(second,timestamp(adddate(date('{}'),0)),timestamp(adddate(date('{}'),0)))/3600,2))FROM tb_first_running a WHERE a.Green='1' AND a.in_time>='{}' AND a.in_time<='{}'",



    #oee-report
    #設備稼動率
    "utilization_rate":"SELECT DATE_FORMAT(a.in_time,'%Y-%m-%d'),a.Controller,round(COUNT(*)/360/24,2) ,b.site, b.building ,  b.floor, b.station, b.provider, b.level_1\
                        FROM tb_first_running a , st126_device b \
                        WHERE  a.Controller=b.gw_name \
                        AND {} \
                        a.Green='1' AND a.Red='0'AND a.Yellow='0' AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 1 day) GROUP BY DATE_FORMAT(a.in_time,'%Y-%m-%d'),a.Controller",

    #平均稼動率
    "Avg_utilization_rate":"SELECT a.Controller,round(COUNT(*)/360/24/(DATEDIFF('{}','{}')+1),2)\
                            FROM tb_first_running a , st126_device b \
                            WHERE  a.Controller=b.gw_name \
                            AND {} \
                            a.Green='1' AND a.Red='0'AND a.Yellow='0' AND a.in_time>=timestamp(adddate(date('{}'),0)) AND a.in_time<=date_add(timestamp(adddate(date('{}'),0)), interval 1 day) \
                            GROUP BY a.Controller",




    #车间平均
    "Avg_station_rate":"SELECT DATE_FORMAT(a.in_time, '%Y-%m-%d'), round(COUNT(*) / 360 / 24/(SELECT count(DISTINCT(a.Controller)) FROM tb_first_running a ,st126_device b WHERE a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}' AND b.sections='{}' AND a.in_time>=date_add(NOW(), interval -1 minute) AND a.in_time<=NOW()),2) ,b.site,b.building,b.floor,b.sections,b.provider,b.level_1\
                        FROM tb_first_running a,st126_device b\
                        WHERE a.Controller = b.gw_name\
                        AND b.site = '{}'AND b.building = '{}'AND b.floor = '{}'AND b.sections = '{}'\
                        AND a.Green = '1'AND a.Red = '0'AND a.Yellow = '0'  \
                        AND a.in_time >= TIMESTAMP (adddate(date('{}'),0))\
                        AND a.in_time <= date_add(TIMESTAMP (adddate(date('{}'),0)),INTERVAL 1 DAY)\
                        GROUP BY DATE_FORMAT(a.in_time, '%Y-%m-%d')",


    "Energy_equipment": "SELECT gw_name,rc,min_energy,max_energy,kwh,site,building,floor,sections,provider,level_1,a_voltage,b_voltage,c_voltage,\
    a_current,b_current,c_current,a_power,b_power,c_power FROM `view_watt_port_detail` WHERE {0}  rc>='{1}' AND rc<='{2}'",

    "Energy_station":"SELECT {0},SUM(kw) FROM `view_actual_watt` WHERE {1} rc>DATE_FORMAT(NOW()- INTERVAL 12 HOUR,'%Y-%m-%d %H:00:00') AND shift=IF(HOUR(NOW()) >= 8 AND HOUR(NOW()) <20, 'D','N') GROUP BY {0}",
    #watt的车间port
     "watt_port":"SELECT DISTINCT(a.port) FROM tb_first_watt a",

    "utilization_rate_by_date": "SELECT rc,ROUND((SUM(a.rt) / sum(a.ot) * 100),2) \
                    FROM (SELECT * FROM `tb_activation_by_day` WHERE rc >= '{}' AND rc <= '{}' ) a \
                    LEFT JOIN sanse_deng_list b ON a.machine = b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name = c.gw_name where {} 1=1 GROUP BY rc",

    "utilization_rate_by_machine": "SELECT gw_name,ROUND((SUM(a.rt) / sum(a.ot) * 100),2) \
                  FROM (SELECT * FROM `tb_activation_by_day` WHERE rc >= '{}' AND rc <= '{}' ) a \
                  LEFT JOIN sanse_deng_list b ON a.machine = b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name = c.gw_name where {} 1=1 GROUP BY gw_name",


    "watt_utilization_rate":"SELECT {3},ROUND(SUM(a.rt) / sum(a.ot), 2) FROM(SELECT * FROM `tb_activation_by_day`\
    ) a LEFT JOIN sanse_deng_list b ON a.machine = b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name = c.gw_name \
    WHERE {0} a.rc>='{1}' AND rc<='{2}' GROUP BY {3}",

    "now_station_Energy":"SELECT h,v,SUM(total),rc FROM `view_watt_by_hour` WHERE \
    {}  rc>='{}' AND rc<'{}'  GROUP BY h ORDER BY rc ",
    "now_station_Energy_by_day":"SELECT DATE_FORMAT(rc,'%Y-%m-%d') AS rc,v,SUM(total) FROM `view_watt_by_hour`\
        WHERE {} DATE_FORMAT(rc,'%Y-%m-%d')>='{}' AND DATE_FORMAT(rc,'%Y-%m-%d')<='{}'\
        GROUP BY DATE_FORMAT(rc,'%Y-%m-%d')",

     "avg_station_Energy":"SELECT round((MAX(a.j_Energy) - MIN(a.j_Energy)) / 24,2)\
FROM tb_first_watt a WHERE DATE_FORMAT(in_time,'%Y-%m-%d')='{}'",

    "Energy_consumption_trend":"SELECT rc,SUM(total)/count(DISTINCT rc) FROM `view_watt_by_hour` \
            WHERE {} rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') AND shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')",



    "all_port_Energy": "SELECT gw_name,SUM(total) FROM `view_watt_by_hour` where {} DATE_FORMAT(rc,'%Y-%m-%d')>='{}' "
                       "AND DATE_FORMAT(rc,'%Y-%m-%d')<='{}' GROUP BY gw_name;",


    #异常三色灯
    "abnormal_sangseding":"SELECT hour(r.in_time),r.Controller,DATE_FORMAT(r.in_time,'%Y-%m-%d %H:%i'),b.site,b.building,b.floor,b.sections,b.provider,b.level_1   \
        FROM tb_first_running r ,st126_device b \
        WHERE r.Controller=b.gw_name \
        AND r.Red='1'AND r.Green='0'AND r.Yellow='0' \
        AND {}\
        r.in_time >= TIMESTAMP (adddate(date('{}'),0)) \
        AND r.in_time < date_add(TIMESTAMP (adddate(date('{}'),0)),INTERVAL 1 DAY) \
        GROUP BY hour(r.in_time),r.Controller",




    "abnormal_th":"SELECT  hour(a.in_time),a.Controller,DATE_FORMAT(a.in_time,'%Y-%m-%d %H:%i') ,b.site,b.building,b.floor,b.sections,b.provider,b.level_1 \
                   FROM tb_first_th a ,st126_device b \
                   WHERE a.Controller=b.gw_name\
                   AND(a.Tem<19 or a.Tem>25 or a.Hum>65)\
                   AND b.site='{}' AND b.building='{}' AND b.floor='{}' \
                   AND a.in_time >= TIMESTAMP (adddate(date('{}'),0))\
                   AND a.in_time < date_add(TIMESTAMP (adddate(date('{}'),0)),INTERVAL 1 DAY)\
                   GROUP BY hour(a.in_time),a.Controller",


    "abnormal_number":"SELECT count(r.Controller),DATE_FORMAT(r.in_time,'%Y-%m-%d')\
                       FROM tb_first_running r ,st126_device b \
                       WHERE r.Controller=b.gw_name\
                       AND ((r.Green='0' and r.Red='1' and r.Yellow='0')OR(r.Green='1' and r.Red='1' and r.Yellow='0'))  \
                       AND {}  \
                       r.in_time >= TIMESTAMP (adddate(date('{}'),0))\
                       AND r.in_time < date_add(TIMESTAMP (adddate(date('{}'),0)),INTERVAL 1 DAY)\
                       GROUP BY DATE_FORMAT(r.in_time,'%Y-%m-%d')",

    #甘特图正常
    "four_state_normal":"SELECT a.Controller, a.state,a.start_time,a.last_time ,b.site ,b.building,b.floor ,b.sections   FROM unm_state a , st126_device b WHERE a.state='1'\
                         AND a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}'  AND b.sections='{}'\
                         AND a.start_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.last_time<=date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)\
                         GROUP BY a.Controller",


    #甘特图异常
    "four_state_abnormal":"SELECT a.Controller, a.state,a.start_time,a.last_time ,b.site ,b.building,b.floor ,b.sections   FROM unm_state a , st126_device b WHERE a.state='2'\
                         AND a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}'  AND b.sections='{}'\
                         AND a.start_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.last_time<=date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)\
                         GROUP BY a.Controller",

    # 甘特图待机
    "four_state_wait": "SELECT a.Controller, a.state,a.start_time,a.last_time ,b.site ,b.building,b.floor ,b.sections   FROM unm_state a , st126_device b WHERE a.state='3'\
                     AND a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}'  AND b.sections='{}'\
                     AND a.start_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.last_time<=date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)\
                     GROUP BY a.Controller",

   # 甘特图停机
    "four_state_down": "SELECT a.Controller, a.state,a.start_time,a.last_time ,b.site ,b.building,b.floor ,b.sections   FROM unm_state a , st126_device b WHERE a.state='4'\
                       AND a.Controller=b.gw_name AND b.site='{}' AND b.building='{}' AND b.floor='{}'  AND b.sections='{}'\
                       AND a.start_time>=date_add(timestamp(adddate(date('{}'),0)), interval 8 hour) AND a.last_time<=date_add(timestamp(adddate(date('{}'),0)), interval 20 hour)\
                       GROUP BY a.Controller",






#By 類型查看每天的稼動率
    "ids_rate_day":"SELECT a.machine,rc,ROUND(SUM(jdl)/count(a.machine),2) jdl from(SELECT machine,rc, \
    ROUND((rt / ot) * 100,2) jdl FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine) \
     a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name \
    WHERE site = '{}' AND building = '{}' AND floor = '{}' AND sections = '{}' GROUP BY a.machine, rc",


    "ids_rate_sun_day":"SELECT a.rc,a.rc,ROUND(SUM(jdl)/count(a.machine),2), sum(jdl)/count(a.machine) from(SELECT machine, rc, \
        ROUND((rt / ot) * 100,2) jdl FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine) \
        a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name WHERE \
	    site = '{}' AND building = '{}' AND floor = '{}' AND sections = '{}' \
        GROUP BY rc",


    "ids_rate_sum_week":"SELECT a.machine ,rc,ROUND(SUM(jdl)/count(a.machine),2) jdl from(SELECT machine,DATE_FORMAT(rc,'%Y_%U') rc, \
    ROUND((rt / ot) * 100,2) jdl FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine)  \
     a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name  \
    WHERE site = '{}' AND building = '{}' AND floor = '{}' AND sections = '{}' GROUP BY a.machine, rc",


    "ids_rate_week":"SELECT level_1,rc,ROUND(SUM(jdl)/count(a.machine),2) jdl,a.rc1 from(SELECT machine,DATE_FORMAT(rc,'WK%u') rc, \
    ROUND((rt / ot) * 100,2) jdl,rc AS rc1 FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine) \
     a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name \
    WHERE site = '{}' AND building = '{}' AND floor = '{}' AND sections = '{}' GROUP BY  rc order by rc1 ",

    "ids_type_rate_day":"SELECT level_1,a.rc,ROUND(SUM(jdl)/count(a.machine),2) jdl from(SELECT machine,rc, \
    ROUND((rt / ot) * 100,2) jdl FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine) \
     a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name WHERE site = '{}' \
AND building = '{}' AND floor = '{}' AND sections = '{}' GROUP BY level_1,rc ORDER BY rc",


    "ids_type_rate_week":"SELECT level_1,a.rc,ROUND(SUM(jdl)/count(a.machine),2) jdl,a.rc1 from(SELECT machine,DATE_FORMAT(rc,'WK%u') rc, \
    ROUND((rt / ot) * 100,2) jdl,rc AS rc1 FROM tb_activation_by_day WHERE rc>='{}' AND rc<='{}' GROUP BY rc,machine) \
     a LEFT JOIN sanse_deng_list b ON a.machine =b.sanse_deng LEFT JOIN st126_device c ON b.equipment_name=c.gw_name WHERE site = '{}' \
AND building = '{}' AND floor = '{}' AND sections = '{}' GROUP BY level_1,rc order by rc1",














    "provider_all":"SELECT DISTINCT provider FROM `sanse_deng_list` a, st126_device b WHERE a.equipment_name=b.gw_name GROUP BY level_1",
    "level_1_all":"SELECT DISTINCT level_1,level_2,level_3 FROM st126_device WHERE provider='{}'",

    "gw_name_all":"SELECT DISTINCT a.gw_name, b.sanse_deng  FROM st126_device a, sanse_deng_list b WHERE a.gw_name = b.equipment_name  AND a.level_1='{}'",



    #
    "port_manage":"SELECT b.provider,b.level_1, b.asset_no, b.property_no ,a.a_Current,a.b_Current,a.c_Current, a.a_Voltage,a.b_Voltage,a.c_Voltage \
        FROM tb_first_watt a ,st126_device b ,Equipment_Bind_Meter_List e,sanse_deng_list s \
        WHERE s.equipment_name=b.gw_name  \
        AND e.equipment_id=s.equipment_id \
        AND s.sanse_deng='{}' \
        AND a.in_time>=date_add(NOW(), interval -1 minute) \
        AND a.in_time <=NOW()",


    "port_abnormal_num":"SELECT COUNT(*) FROM  unm_state a WHERE a.Controller='{}' AND a.state='4' and start_time BETWEEN '{}' AND '{}'",
#當前溫濕度
   # "now_th":"SELECT  MAX(rc),avg(tem),avg(hum) FROM view_defect_hum_tem ",

   "now_th":"SELECT h,avg_tem,avg_hum,v FROM `vies_th_by_hour` WHERE \
             rc> DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') AND shift = IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N') \
             GROUP BY h ORDER BY h\
             DESC LIMIT 1",

#當前溫濕度異常區域
    "defect_tem_now":"SELECT a.Controller,b.tem  FROM(SELECT MAX(rc) rc,Controller FROM `view_defect_hum_tem` GROUP BY Controller) a \
                      LEFT JOIN view_defect_hum_tem b ON a.Controller=b.Controller AND a.rc=b.rc WHERE fail_tem=1",
    "defect_hum_now":"SELECT a.Controller,b.tem  FROM(SELECT MAX(rc) rc,Controller FROM `view_defect_hum_tem` GROUP BY Controller) a \
                      LEFT JOIN view_defect_hum_tem b ON a.Controller=b.Controller AND a.rc=b.rc WHERE fail_hum=1",



#異常統計設備異常次數，溫思度異常次數，然後是detail
    "defect_machine":"SELECT SUM(fail) FROM `view_defect_controller`  WHERE {} rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') AND  \
                     shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')",

    "defect_hum_tem":"SELECT SUM(fail_hum),sum(fail_tem) FROM `view_defect_hum_tem` \
                     WHERE {} rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') \
                     AND shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')",
    "defect_hum_detail":"SELECT CONCAT(site,'-',building,'-',floor),rc,'濕度異常',sections,level_1,gw_name FROM `view_defect_hum_tem` \
        WHERE {} fail_hum=1 AND rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') \
      AND shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')" ,
    "defecet_tem_detail":"SELECT CONCAT(site,'-',building,'-',floor),rc,'溫度異常',sections,level_1,gw_name FROM `view_defect_hum_tem` \
                    WHERE {} fail_tem=1 AND rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') \
                   AND shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')",
    "defect_machine_detail":"SELECT CONCAT(site,'-',building,'-',floor),rc,'三色燈異常',sections,level_1,gw_name FROM `view_defect_controller` \
                        WHERE  fail=1 AND rc > DATE_FORMAT(NOW() - INTERVAL 12 HOUR,'%Y-%m-%d %H:%i:00') \
                        AND shift =IF (HOUR (NOW()) >= 8 AND HOUR (NOW()) < 20,'D','N')",


    "defect_by_day":"SELECT a.rc,SUM(a.fail+b.fail) FROM \
                  (SELECT DATE_FORMAT(rc,'%Y-%m-%d') AS rc,SUM(fail) AS fail FROM `view_defect_controller`  WHERE fail=1 and  {} DATE_FORMAT(rc,'%Y-%m-%d')>='{}' \
                  AND DATE_FORMAT(rc,'%Y-%m-%d')<='{}'  GROUP BY DATE_FORMAT(rc,'%Y-%m-%d')) a LEFT JOIN \
                  (SELECT DATE_FORMAT(rc,'%Y-%m-%d') AS rc,SUM(fail_hum+fail_tem) as fail FROM view_defect_hum_tem  WHERE \
                  DATE_FORMAT(rc,'%Y-%m-%d')>='{}' \
                  AND DATE_FORMAT(rc,'%Y-%m-%d')<='{}'\
                  GROUP BY DATE_FORMAT(rc,'%Y-%m-%d')) b ON a.rc=b.rc \
                  GROUP BY a.rc",

####新版ST sql
#氣壓
    "PRESSURE_COUNT":"SELECT DATE_FORMAT(rc,'%H:%i'),IF(a.pressure=65535,0,FORMAT(a.pressure/1000,2)) FROM \
                      (SELECT gw_id,rc,pressure FROM `tb_pressure` WHERE DATE_FORMAT(rc, '%Y-%m-%d') = DATE_FORMAT('{}','%Y-%m-%d') ) \
                      a LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE gw_name='{}' ORDER BY rc ASC" ,

    "PRESSURE_ONE":"SELECT DATE_FORMAT(rc,'%H:%i'),IF (a.pressure = 65535,0,FORMAT(a.pressure / 1000, 2)) \
                   FROM`tb_pressure_state` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE gw_name = '{}' AND rc>='{}'",

#溫濕度
    "TOTAL_TH_STATE":"SELECT {},FORMAT(AVG(hum),1),FORMAT(AVG(tem),1) FROM `tb_th_state` a LEFT JOIN st126_device b \
                      ON a.controller=b.gw_name WHERE  sections is not null {} GROUP BY {}",

    "TOTAL_TH_TREND":"SELECT DATE_FORMAT(a.rc,'%H:%i') as rc,{},FORMAT(AVG(hum), 1) hum,FORMAT(AVG(tem), 1) tem FROM `tb_th_02` a LEFT JOIN \
                        st126_device b ON a.gw_id = b.gw_id WHERE sections is not null {} and date(a.rc)='{}' GROUP BY a.rc,{}",

    "TOTAL_TH_TREND_DAY":"SELECT DATE_FORMAT(rc, '%Y-%m-%d'),{},FORMAT(AVG(hum), 1),FORMAT(AVG(tem), 1) FROM \
                            (SELECT rc,gw_id,AVG(hum) hum,AVG(tem) tem FROM `tb_th_02` WHERE  date(rc)>='{}' AND date(rc)<='{}' \
                            GROUP BY DATE_FORMAT(rc, '%Y-%m-%d'),gw_id) a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                            WHERE sections is not null {}  GROUP BY DATE_FORMAT(rc, '%Y-%m-%d'),{}",

    "SES_TH_STATE":"SELECT DATE_FORMAT(rc, '%Y-%m-%d'),{},FORMAT(AVG(a.avg_tem), 1),FORMAT(MAX(a.max_tem), 1),FORMAT(MIN(a.min_tem), 1),\
                     FORMAT(AVG(a.avg_hum), 1),FORMAT(MAX(a.max_hum), 1),FORMAT(MIN(a.min_hum), 1) \
                     FROM(SELECT rc,gw_id,AVG(tem) avg_tem,MAX(tem) max_tem,\
                     min(tem) min_tem,AVG(hum) avg_hum,MAX(hum) max_hum,min(hum) min_hum FROM `tb_th_02` \
                     WHERE date(rc) >= '{}' AND date(rc) <= '{}'  GROUP BY DATE_FORMAT(rc, '%Y-%m-%d'),gw_id) a \
                     LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                     WHERE sections IS NOT NULL AND sections='{}' GROUP BY DATE_FORMAT(rc, '%Y-%m-%d'),{}",
#設備運行狀態
    "MACHINE_GANTT":"SELECT gw_name,state,nomal,exception,standby,`stop`,lost FROM \
                    (SELECT * FROM `tb_machine_gantt` WHERE start_time >= '{}') \
                    a LEFT JOIN st126_device b ON a.gw_id = b.gw_id  {} ORDER BY start_time",

    "MACHINE_GANTT_DAY": "SELECT gw_name,state,nomal,exception,standby,`stop`,lost FROM \
                (SELECT * FROM `tb_machine_gantt` WHERE date(start_time) = '{}') \
                a LEFT JOIN st126_device b ON a.gw_id = b.gw_id  {} ORDER BY start_time",

    "MACHINE_GANTT1":"SELECT b.gw_name,a.state,a.nomal,a.`stop`\
                 FROM `tb_meter_gantt_new` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                WHERE a.start_time >= '{}' AND {} ORDER BY a.start_time",

    "MACHINE_GANTT_DAY1": "SELECT b.gw_name,a.state,a.nomal,a.`stop` \
         FROM `tb_meter_gantt_new` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
        WHERE date(a.start_time)= '{}' AND {} ORDER BY a.start_time",

    "SAN_GANTT":"SELECT b.gw_name,a.state,a.nomal,a.exception,a.standby,a.`stop`,a.lost \
                 FROM `tb_machine_gantt` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                WHERE a.start_time >= '{}' AND {} ORDER BY a.start_time",

    "SAN_GANTT_DAY": "SELECT b.gw_name,a.state,a.nomal,a.exception,a.standby,a.`stop`,a.lost \
             FROM `tb_machine_gantt` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
            WHERE date(a.start_time)= '{}' AND {} ORDER BY a.start_time",




    "JDL_DAY":"SELECT rc,FORMAT(SUM(rt) / SUM(ot) * 100, 2) FROM tb_meter_activation_by_day a \
LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE rc >= '{}' AND rc <= '{}' {} GROUP BY rc",

    "JDL_TREND":"SELECT {0},FORMAT(SUM(rt)/SUM(ot)*100,2) jdl FROM tb_meter_activation_by_day a \
                  LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                  WHERE rc>='{1}' AND rc<='{2}'  AND {0} is not NULL {3} GROUP BY {0} ORDER BY jdl DESC",
    "MACHINE_FORMS":"SELECT rc,site,building,floor,sections,provider,level_1,gw_name,\
            FORMAT(SUM(rt) / SUM(ot) * 100, 2) jdl FROM tb_meter_activation_by_day a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
            WHERE rc >= '{}' AND rc <= '{}' {} AND gw_name is not null \
             GROUP BY rc,gw_name ORDER BY rc desc,gw_name",

#能耗監控
    "ENERGY_TREND":"SELECT {0},SUM(energy) FROM view_watt_by_hours1 WHERE {1}  GROUP BY {0}",
    "TOTAL_ENERGY":"SELECT SUM(energy) FROM view_watt_by_hours1 WHERE {}" ,
    "SAN_ENERGY_ID": "SELECT gw_id,gw_name,SUM(energy) FROM view_watt_by_hours1 WHERE {} GROUP BY gw_id",
    #計算三色燈各狀態
    "SAN_STATE":"SELECT (SUM(nomal)+SUM(standby))/SUM(300),(SUM(exception)+sum(stop))/SUM(300),SUM(nomal)/SUM(300) \
                FROM tb_machine_gantt WHERE {} AND gw_id IN (SELECT DISTINCT gw_id FROM view_watt_by_hours1 WHERE {})",

    "SAN_STATE_ID":"SELECT gw_id,IFNULL((SUM(nomal)+SUM(standby))/SUM(300),0),\
                   IFNULL((SUM(exception)+sum(stop))/SUM(300),0),IFNULL(SUM(nomal)/SUM(300),0) FROM tb_machine_gantt \
                 WHERE {} AND gw_id IN (SELECT DISTINCT gw_id FROM view_watt_by_hours1 WHERE {}) GROUP BY gw_id",

    "WATT_FROMS":"SELECT  concat(site,'_',building,'_',floor,'_',sections),provider,level_1,gw_id,gw_name,SUM(energy) \
                   FROM view_watt_by_hours1 WHERE {} GROUP BY gw_id"

}
class Database:
    def __init__(self,host,port,user,password,dbname):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = password
        self.db = dbname
        self.dbtype = 'mysql'
        self.conn = None
        self.cur = None
        self.charset = 'utf8'

    def get_conn(self):
        if self.dbtype == 'mysql':
            return pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
        else:
            return False

    def get_cursor(self):
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
            return True
        except Exception as err:
            #print('db %s failed,Message:%s' % (self.dbtype, err))
            return False

    def close(self):
        self.cur.close()
        self.conn.close()

    def select_table(self,sql):
        try:
            self.cur.execute(sql)
            rows = self.cur.fetchall()
            return rows
        except Exception as err:
            #print('Message:%s' % (err))
            return  False

    def insert_table(self, sql):
        try:
            self.cur.execute(sql)
            a = self.conn.commit()
            return a
        except Exception as err:
            #print('Message:%s' % err)
            return  False

    def delect(self,sql):
        try:
            self.cur.execute(sql)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()

def conn_ims(sql):
    db = Database(host=stg.DATABASES['db2']['HOST'], port=stg.DATABASES['db2']['PORT'],
                  user=stg.DATABASES['db2']['USER'],
                  password=stg.DATABASES['db2']['PASSWORD'],
                  dbname=stg.DATABASES['db2']['NAME'])
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def my_select1(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def delect_data(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    db.delect(sql)
    db.close()


def insert_data(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    db.insert_table(sql)
    db.close()


red = redis.Redis(host=stg.REDIS_HOST, port=stg.REDIS_PORT,
                  db=6, password=stg.REDIS_PASSWORD)


def red_conn(key):
    try:
        if red.exists(key):
            red_data = red.get(key)
            return red_data.decode('utf-8')
        else:
            return None
    except Exception as e:
        return None


def get_keys(key):
    try:
        keys = red.keys(key)
        return keys
    except Exception as e:
        return []
